package yagwl.service.demo;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.logging.Logger;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import yagwl.service.util.db.DaoContext;

public class LaureateDao {

  private static final Logger log = Logger.getLogger(LaureateDao.class
      .getName());

  private DaoContext ctx;

  public LaureateDao(DaoContext ctx) {
    this.ctx = ctx;
  }

  public List<LaureateEntity> getLaureates(int startIndex, int fetchSize,
      int sortOrder) {
    String orderBy = getOrderBy(sortOrder);
    EntityManager em = ctx.em();
    String qs = "select l from LaureateEntity l" + orderBy;
    log.info(qs);
    Query query = em.createQuery(qs);
    query.setFirstResult(startIndex);
    query.setMaxResults(fetchSize);

    log.info(query.toString());
    @SuppressWarnings("unchecked")
    List<LaureateEntity> laureates = (List<LaureateEntity>) query
        .getResultList();
    log.info("Fetched laureates form DB: " + laureates.size());
    return laureates;
  }

  public List<LaureateEntity> getLaureates(int startIndex, int fetchSize,
      int sortOrder, String category, int yearStart, int yearEnd,
      List<String> countryList) {
    log.info("startIndex: " + startIndex + ", fetchSize: " + fetchSize
        + ", sortOrder: " + sortOrder + ", category: " + category
        + ", yearStart: " + yearStart + ", yearEnd: " + yearEnd
        + ", countryList: " + countryList);
    StringBuffer qs = new StringBuffer("select l from LaureateEntity l");
    boolean whereAdded = false;
    if (countryList != null && countryList.size() > 0) {
      qs.append(whereAdded ? " and l.country in (" : " where l.country in (");
      ;
      for (int i = 0; i < countryList.size(); i++) {
        qs.append(i == 0 ? "'" + countryList.get(i) + "'" : ", '" + countryList.get(i) + "'");
      }
      qs.append(")");
      whereAdded = true;
      sortOrder = sortOrder == 0 ? LaureateConstants.SortCountryAsc : sortOrder;
    }
    if (category != null) {
      // qs.append(whereAdded ? " and l.category = :category" : " where l.category = :category");
      qs.append((whereAdded ? " and" : " where" ) + " l.category = '" + category + "'");
      whereAdded = true;
      sortOrder = sortOrder == 0 ? LaureateConstants.SortCatAscYearAsc : sortOrder;
    }
    if (yearStart > 0) {
      // qs.append(whereAdded ? " and l.year >= :yearStart" : " where l.year >= :yearStart");
      qs.append((whereAdded ? " and" : " where" ) + " l.year >= " + yearStart);
      whereAdded = true;
      // sortOrder = sortOrder == 0 ? LaureateConstants.SortYearAsc : sortOrder;
    }
    if (yearEnd > 0) {
      // qs.append(whereAdded ? " and l.year <= :yearEnd" : " where l.year <= :yearEnd");
      qs.append((whereAdded ? " and" : " where" ) + " l.year <= " + yearEnd);
      whereAdded = true;
      // sortOrder = sortOrder == 0 ? LaureateConstants.SortYearAsc : sortOrder;
    }
    /*
    if (countryList != null && countryList.size() > 0) {
      qs.append(whereAdded ? " and l.country in (" : " where l.country in (");
      int i = 1;
      for (; i <= countryList.size(); i++) {
        qs.append(i == 1 ? ":1" : ", :" + i);
      }
      qs.append(", :" + i); // bug? having to add the last item twice
      qs.append(")");
      whereAdded = true;
      sortOrder = sortOrder == 0 ? LaureateConstants.SortCountryAsc : sortOrder;
    }
    */
    qs.append(getOrderBy(sortOrder));
    log.info(qs.toString());
    EntityManager em = ctx.em();
    Query query = em.createQuery(qs.toString());
    /*
    if (yearStart > 0) {
      query.setParameter("yearStart", yearStart);
    }
    if (yearEnd > 0) {
      query.setParameter("yearEnd", yearEnd);
    }
    if (category != null)
      query.setParameter("category", category);
    if (countryList != null && countryList.size() > 0) {
      int i = 1;
      for (; i <= countryList.size(); i++) {
        query.setParameter(i, countryList.get(i - 1));
        log.info(countryList.get(i - 1));
      }
      query.setParameter(i, countryList.get(i - 2)); // bug? having to add the
                                                     // last item twice
    }
    */
    query.setFirstResult(startIndex);
    query.setMaxResults(fetchSize);

    @SuppressWarnings("unchecked")
    List<LaureateEntity> laureates = (List<LaureateEntity>) query
        .getResultList();
    log.info("Fetched laureates form DB: " + laureates.size());
    return laureates;
  }

  private String getOrderBy(int sortOrder) {
    String orderBy = "";
    switch (sortOrder) {
    case LaureateConstants.SortYearAsc:
      orderBy = " order by year asc, category asc, name asc";
      break;
    case LaureateConstants.SortYearDesc:
      orderBy = " order by year desc, category asc, name asc";
      break;
    case LaureateConstants.SortCatAscYearAsc:
      orderBy = " order by category asc, year asc";
      break;
    case LaureateConstants.SortCatAscYearDesc:
      orderBy = " order by category asc, year desc";
      break;
    case LaureateConstants.SortCatDescYearAsc:
      orderBy = " order by category desc, year asc";
      break;
    case LaureateConstants.SortCatDescYearDesc:
      orderBy = " order by category desc, year desc";
      break;
    case LaureateConstants.SortCountryAsc:
      orderBy = " order by country asc, year asc, category asc";
      break;
    case LaureateConstants.SortCountryDesc:
      orderBy = " order by country desc, year asc, category asc";
      break;
    }
    return orderBy;
  }

  public List<String> getCountries() {
    EntityManager em = ctx.em();
    Query q = em.createQuery("select distinct country from LaureateEntity "
        + "order by country");
    List<String> list = null;
    try {
      list = (List<String>) q.getResultList();
    } catch (Exception e) {
      log.severe(e.toString());
      list = new ArrayList<String>();
    }
    log.finer(list.toString());

    // GAE limitation: distinct doesn't work
    Set<String> set = new HashSet<String>(list);
    List<String> distinct = new ArrayList<String>(set);
    Collections.sort(distinct);

    return distinct;
  }
  
  private class LaureateConstants {

    public static final int SortYearAsc = 1;
    public static final int SortYearDesc = 2;
    public static final int SortCatAscYearAsc = 3;
    public static final int SortCatAscYearDesc = 4;
    public static final int SortCatDescYearAsc = 5;
    public static final int SortCatDescYearDesc = 6;
    public static final int SortCountryAsc = 7;
    public static final int SortCountryDesc = 8;
  }

}
